﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;
using EffiProzDB.Lib;
using EffiProzDB.DataTypes;
using EffiProzDB.Persist;

namespace EffiProzDBTest
{
    /// <summary>
    /// Summary description for TestSubselect
    /// </summary>
    [TestClass]
    public class TestSubselect : BaseTest
    {
        public TestSubselect()
        {
          
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        void createDataset()
        {

            CleanUp("TestSubselect");
            sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "TestSubselect",
                   user, password, props);

            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "drop table colors if exists; "
                               + "drop table sizes if exists; "
                               + "drop table fruits if exists; "
                               + "drop table trees if exists; ");

            TestHelper.ExecuteUpdateSQL_Success(sessionProxy,
                 "create table colors(id int, val char); "
                 + "insert into colors values(1,'red'); "
                 + "insert into colors values(2,'green'); "
                 + "insert into colors values(3,'orange'); "
                 + "insert into colors values(4,'indigo'); "
                 + "create table sizes(id int, val char); "
                 + "insert into sizes values(1,'small'); "
                 + "insert into sizes values(2,'medium'); "
                 + "insert into sizes values(3,'large'); "
                 + "insert into sizes values(4,'odd'); "
                 + "create table fruits(id int, name char, color_id int); "
                 + "insert into fruits values(1, 'golden delicious',2); "
                 + "insert into fruits values(2, 'macintosh',1); "
                 + "insert into fruits values(3, 'red delicious',1); "
                 + "insert into fruits values(4, 'granny smith',2); "
                 + "insert into fruits values(5, 'tangerine',4); "
                 + "create table trees(id int, name char, fruit_id int, size_id int); "
                 + "insert into trees values(1, 'small golden delicious tree',1,1); "
                 + "insert into trees values(2, 'large macintosh tree',2,3); "
                 + "insert into trees values(3, 'large red delicious tree',3,3); "
                 + "insert into trees values(4, 'small red delicious tree',3,1); "
                 + "insert into trees values(5, 'medium granny smith tree',4,2); ");

        }



     /**
     * This test is basically a sanity check of the data set.
     */
        public void testSimpleJoin()
        {

            String sql =
                "select trees.id, trees.name, sizes.val, fruits.name, colors.val"
                + " from trees, sizes, fruits, colors"
                + " where trees.size_id = sizes.id"
                + " and trees.fruit_id = fruits.id"
                + " and fruits.color_id = colors.id" + " order by 1";
            int expectedRows = 5;
            String[] expectedTrees = new String[] {
            "small golden delicious tree", "large macintosh tree",
            "large red delicious tree", "small red delicious tree",
            "medium granny smith tree"
        };
            String[] expectedSizes = new String[] {
            "small", "large", "large", "small", "medium"
        };
            String[] expectedFruits = new String[] {
            "golden delicious", "macintosh", "red delicious", "red delicious",
            "granny smith"
        };
            String[] expectedColors = new String[] {
            "green", "red", "red", "red", "green"
        };

            Result results = TestHelper.ExecuteSelectSQL(sessionProxy, sql, null);
            String[] trees = new String[expectedRows];
            String[] fruits = new String[expectedRows];
            String[] sizes = new String[expectedRows];
            String[] colors = new String[expectedRows];


            for (int rowCount = 0; rowCount < expectedRows; rowCount++)
            {
                TestHelper.MatchSingleResultRow(results, rowCount,rowCount+1, expectedTrees[rowCount], expectedSizes[rowCount],
                    expectedFruits[rowCount], expectedColors[rowCount]);

                rowCount++;
            }
        }


         /**
     * Inner select with where clause in outer select having column with same name as where clause in inner select
     */
    public void testWhereClausesColliding()  {

        String sql =
            "select name from fruits where id in (select fruit_id from trees where id < 3) order by name";
        String[] expected = new String[] {
            "golden delicious", "macintosh"
        };

        Result results = TestHelper.ExecuteSelectSQL(sessionProxy, sql, expected.Length);
        for (int rowCount = 0; rowCount < expected.Length; rowCount++)
        {
            TestHelper.MatchSingleResultRow(results, rowCount, expected[rowCount]);
        }
       
    }

        /**
     * As above, with table aliases.
     */
    public void testWhereClausesCollidingWithAliases() {

        String sql =
            "select a.name from fruits a where a.id in (select b.fruit_id from trees b where b.id < 3) order by name";
        String[] expected = new String[] {
            "golden delicious", "macintosh"
        };

        Result results = TestHelper.ExecuteSelectSQL(sessionProxy, sql, expected.Length);
        for (int rowCount = 0; rowCount < expected.Length; rowCount++)
        {
            TestHelper.MatchSingleResultRow(results, rowCount, expected[rowCount]);
        }
    }

         /**
     * Inner select with two tables having columns with the same name, one of which is referred to in the
     * subselect, the other of which is not used in the query (both FRUITS and TREES have NAME column,
     * but we're only selecting FRUITS.NAME and we're not referring to TREES.NAME at all).
     */
    public void testHiddenCollision()  {

        String sql =
            "select name from fruits where id in (select fruit_id from trees) order by name";
        String[] expected = new String[] {
            "golden delicious", "granny smith", "macintosh", "red delicious"
        };

        Result results = TestHelper.ExecuteSelectSQL(sessionProxy, sql, expected.Length);
        for (int rowCount = 0; rowCount < expected.Length; rowCount++)
        {
            TestHelper.MatchSingleResultRow(results, rowCount, expected[rowCount]);
        }
    }

         /**
     * As above, with table aliases.
     */
    public void testHiddenCollisionWithAliases() {

        String sql =
            "select a.name from fruits a where a.id in (select b.fruit_id from trees b) order by a.name";
        String[] expected = new String[] {
            "golden delicious", "granny smith", "macintosh", "red delicious"
        };

        compareResults(sql, expected);
    }

    public void compareResults(string sql, string[] expected)
    {
        Result results = TestHelper.ExecuteSelectSQL(sessionProxy, sql, expected.Length);
        for (int rowCount = 0; rowCount < expected.Length; rowCount++)
        {
            TestHelper.MatchSingleResultRow(results, rowCount, expected[rowCount]);
        }
    }


         /**
     * Inner select with where clause in outer select having column with same name as select clause in inner select
     */
    public void testWhereSelectColliding()  {

        // Yes, this is a nonsensical query
        String sql =
            "select val from colors where id in (select id from trees where fruit_id = 3) order by val";
        String[] expected = new String[] {
            "indigo", "orange"
        };

        compareResults(sql, expected);
    }

        /**
     * As above, with aliases.
     */
    public void testWhereSelectCollidingWithAliases()  {

        // Yes, this is a nonsensical query
        String sql =
            "select a.val from colors a where a.id in (select b.id from trees b where b.fruit_id = 3) order by a.val";
        String[] expected = new String[] {
            "indigo", "orange"
        };

        compareResults(sql, expected);
    }


         /**
     * Inner select involving same table
     */
    public void testSameTable()  {

        String sql =
            "select name from trees where id in (select id from trees where fruit_id = 3) order by name";
        String[] expected = new String[] {
            "large red delicious tree", "small red delicious tree"
        };

        compareResults(sql, expected);
    }

         /**
     * As above with aliases.
     */
    public void testSameTableWithAliases() {

        String sql =
            "select a.name from trees a where a.id in (select b.id from trees b where b.fruit_id = 3) order by a.name";
        String[] expected = new String[] {
            "large red delicious tree", "small red delicious tree"
        };

        compareResults(sql, expected);
    }

         /**
     *     Inner select involving same table as one of two joined tables in outer select
     */
    public void testSameTableWithJoin() {

        String sql =
            "select sizes.val from trees, sizes where sizes.id = trees.size_id and trees.id in (select id from trees where fruit_id = 3) order by sizes.val";
        String[] expected = new String[] {
            "large", "small"
        };

        compareResults(sql, expected);
    }

    /**
     * Tests two subselects, anded.
     */
    public void testAndedSubselects() {

        String sql =
            "select name from trees where size_id in (select id from sizes where val = 'large') and fruit_id in (select id from fruits where color_id = 1) order by name";
        String[] expected = new String[] {
            "large macintosh tree", "large red delicious tree"
        };

        compareResults(sql, expected);
    }

        /**
     * Test nested subselects.
     */
    public void testNestedSubselects()  {

        String sql =
            "select name from trees where fruit_id in (select id from fruits where color_id in (select id from colors where val = 'red')) order by name";
        String[] expected = new String[] {
            "large macintosh tree", "large red delicious tree",
            "small red delicious tree"
        };

        compareResults(sql, expected);
    }

    /**
     * Inner select with "not in" in outer select where clause.
     */
    public void testNotIn() {

        String sql =
            "select name from fruits where id not in (select fruit_id from trees) order by name";
        String[] expected = new String[]{ "tangerine" };

        compareResults(sql, expected);
    }

    /**
     * Inner select with "not in" in outer select where clause and same table in inner select where clause.
     */
    public void testNotInSameTableAndColumn()  {

        String sql =
            "select name from fruits where id not in (select id from fruits where color_id > 1 ) order by name";
        String[] expected = new String[] {
            "macintosh", "red delicious"
        };

        compareResults(sql, expected);
    }

         /**
     * Inner select reusing alias names from outer select, but using them for different tables
     */
    public void testAliasScope() {

        String sql =
            "select a.val, b.name from sizes a, trees b where a.id = b.size_id and b.id in (select a.id from trees a, fruits b where a.fruit_id = b.id and b.name='red delicious') order by a.val";
        String[] expectedSizes = new String[] {
            "large", "small"
        };
        String[] expectedTrees = new String[] {
            "large red delicious tree", "small red delicious tree"
        };

        Assert.AreEqual(expectedSizes.Length, expectedTrees.Length,
            "Programmer error: expected arrays should be of equal length.");


        Result results = TestHelper.ExecuteSelectSQL(sessionProxy, sql, expectedTrees.Length);


        for (int rowCount = 0; rowCount < expectedTrees.Length; rowCount++)
        {
            TestHelper.MatchSingleResultRow(results, rowCount, expectedSizes[rowCount], expectedTrees[rowCount]);
        }

    
    }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
    public void Subselect()
        {
            createDataset();
            testSimpleJoin();
            testWhereClausesColliding();
            testWhereClausesCollidingWithAliases();
            testWhereSelectColliding();
            testWhereSelectCollidingWithAliases();
            testSameTable();
            testSameTableWithAliases();
            testSameTableWithJoin();
            testAndedSubselects();
            testNestedSubselects();
            testNotIn();
            testNotInSameTableAndColumn();
        }
    }
}
